import sqlite3
import yaml
from pathlib import Path
from typing import List, Dict, Optional, Tuple

# 定义SQLite变量限制
SQLITE_MAX_VARIABLES = 900

class Database:
    """
    处理所有与SQLite数据库的交互。
    """
    def __init__(self, db_path: str = 'data/db/fingerprints.db'):
        self.db_path = Path(db_path)
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self.conn = None

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def connect(self):
        self.conn = sqlite3.connect(str(self.db_path))
        # --- 优化：启用WAL模式以增强并发读取性能 ---
        # This allows multiple processes to read from the database simultaneously,
        # which is crucial for parallel testing.
        self.conn.execute('PRAGMA journal_mode=WAL;')
        self.conn.execute('PRAGMA foreign_keys = ON;')

    def close(self):
        if self.conn:
            self.conn.close()

    def initialize_schema(self, schema_path: str):
        with open(schema_path, 'r', encoding='utf-8') as f:
            schema = f.read()
        with self.conn:
            self.conn.executescript(schema)
        print("Database schema initialized successfully.")

    def check_duplicate_song(self, file_path: str, config: Dict, threshold: float = 0.9) -> Optional[int]:
        """
        通过比较音频指纹来检查歌曲是否已存在。
        """
        from src.fingerprinting.processor import Processor
        from src.fingerprinting.hashing import Hasher

        processor = Processor(config)
        hasher = Hasher(config['hashing'])

        peaks = processor.audio_to_peaks(file_path)
        if not peaks.any():
            return None

        new_hashes = {h for h, _ in hasher.peaks_to_fingerprints(peaks)}
        if not new_hashes:
            return None

        matches = self.find_matches(list(new_hashes))
        if not matches:
            return None

        song_counts = {}
        for h_matches in matches.values():
            for song_id, _ in h_matches:
                song_counts[song_id] = song_counts.get(song_id, 0) + 1

        if not song_counts:
            return None

        best_match_id, best_count = max(song_counts.items(), key=lambda x: x[1])
        match_ratio = best_count / len(new_hashes)

        return best_match_id if match_ratio >= threshold else None

    def add_song(self, name: str, file_path: str, duration: int, config: Dict, artist: Optional[str] = None, skip_duplicate: bool = True) -> Optional[int]:
        """
        添加一首歌曲到数据库。
        """
        if skip_duplicate:
            from src.fingerprinting.processor import Processor
            from src.fingerprinting.hashing import Hasher
            processor = Processor(config)
            hasher = Hasher(config['hashing'])
            peaks = processor.audio_to_peaks(file_path)
            if not peaks.any(): return None
            new_hashes = {h for h, _ in hasher.peaks_to_fingerprints(peaks)}
            if not new_hashes: return None
            matches = self.find_matches(list(new_hashes))
            if matches:
                song_counts = {}
                for h_matches in matches.values():
                    for song_id, _ in h_matches:
                        song_counts[song_id] = song_counts.get(song_id, 0) + 1
                if song_counts:
                    best_match_id, best_count = max(song_counts.items(), key=lambda x: x[1])
                    if best_count / len(new_hashes) >= 0.9:
                        return None # Skip duplicate
        sql = '''INSERT INTO songs (name, artist, file_path, duration, filename) VALUES (?, ?, ?, ?, ?)'''
        filename = Path(file_path).name
        with self.conn:
            cursor = self.conn.execute(sql, (name, artist, str(file_path), duration, filename))
            return cursor.lastrowid

    def get_song_by_id(self, song_id: int) -> Optional[Dict]:
        sql = 'SELECT id, name, artist, file_path FROM songs WHERE id = ?'
        cursor = self.conn.execute(sql, (song_id,))
        row = cursor.fetchone()
        if row:
            return {'id': row[0], 'name': row[1], 'artist': row[2], 'file_path': row[3]}
        return None

    def get_all_songs(self) -> List[Dict]:
        sql = 'SELECT id, name, artist, file_path FROM songs'
        cursor = self.conn.execute(sql)
        return [{'id': row[0], 'name': row[1], 'artist': row[2], 'file_path': str(row[3])} for row in cursor.fetchall()]

    def add_fingerprints(self, fingerprints: List[Tuple[int, str, int]]):
        sql = 'INSERT INTO fingerprints (song_id, hash, offset) VALUES (?, ?, ?)'
        with self.conn:
            self.conn.executemany(sql, fingerprints)

    def find_matches(self, hashes: List[str]) -> Dict[str, List[Tuple[int, int]]]:
        if not hashes:
            return {}

        # 将哈希列表分割成多个小块
        hash_chunks = [hashes[i:i + SQLITE_MAX_VARIABLES] for i in range(0, len(hashes), SQLITE_MAX_VARIABLES)]
        matches = {}

        # 对每个小块执行一次查询
        for chunk in hash_chunks:
            placeholders = ','.join(['?'] * len(chunk))
            sql = f'SELECT hash, song_id, offset FROM fingerprints WHERE hash IN ({placeholders})'

            cursor = self.conn.execute(sql, chunk)

            # 将每次查询的结果汇总到总的 matches 字典中
            for h, song_id, offset in cursor.fetchall():
                if h not in matches:
                    matches[h] = []
                matches[h].append((song_id, offset))

        return matches

    def search_songs(self, query: str) -> List[Dict]:
        """模糊搜索歌曲。"""
        sql = "SELECT id, name, artist, file_path FROM songs WHERE name LIKE ? OR artist LIKE ?"
        search_term = f"%{query}%"
        cursor = self.conn.execute(sql, (search_term, search_term))
        return [{'id': row[0], 'name': row[1], 'artist': row[2], 'file_path': str(row[3])} for row in cursor.fetchall()]
